package cn.edu.swu;

import cn.edu.swu.book.Book;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class JDBCTest {

    private Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/bookstore";
        String dbUser  = "root";
        String dbPass  = "123456";
        Connection connection = DriverManager.getConnection(jdbcUrl,dbUser,dbPass);
        return connection;
    }

   /* public void createTable() throws SQLException{
        try(Connection connection = this.getConnection()){
            try(Statement statement = connection.createStatement()){
                String sql ="CREATE TABLE book("+
                        "id INT PRIMARY KEY AUTO_INCREMENT,"+
                        "name VARCHAR(50) NOT NULL,"+
                        ""
            }
        }
    }*/

    @Test
    public void createTable() throws SQLException{
        String sql = "CREATE TABLE book (" +
                "id INT PRIMARY KEY AUTO_INCREMENT, " +
                "name VARCHAR(50) NOT NULL," +
                "author VARCHAR(50)," +
                "price DOUBLE," +
                "memo TEXT," +
                "publish DATE)";
        try(Connection connection = this.getConnection()) {
            try(Statement statement = connection.createStatement()){
                statement.execute(String.format(sql));
            }
        }
    }

    @Test
    public void selectTest() throws SQLException {
        List<Book> books = new ArrayList<>();
        try(Connection connection = this.getConnection()) {
            try(Statement statement = connection.createStatement()){
                String sql = "SELECT * FROM book";
                ResultSet resultSet = statement.executeQuery(sql);
                while(resultSet.next()) {
                    Book book = new Book();
                    book.setId(resultSet.getInt("id"));
                    book.setName(resultSet.getString("name"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setPrice(resultSet.getDouble("price"));
                    book.setMemo(resultSet.getString("memo"));
                    book.setPublish(resultSet.getDate("publish"));
                    books.add(book);
                }
            }
        }

        books.forEach(e -> System.out.println(e.toString()));
    }

    @Test
    public void insertTest() throws SQLException{
        try(Connection connection = this.getConnection()) {
            try(Statement statement = connection.createStatement()){
                String template = "INSERT INTO book (name, author, price, memo, publish) " +
                        "values ('%s', '%s', %s, '%s', '%s')";
                statement.execute(String.format(template, "生命中所有的灿烂都", "徐东", 199, "长江新世纪", "2022-08-15"));
                statement.execute(String.format(template, "青春期成长指南", "吴艺真", 28, "黄海珠", "2022-09-15"));
                statement.execute(String.format(template, "经济学通识课", "尼尔·基什特尼", 64.6, "一本好书", "2022-10-15"));
                statement.execute(String.format(template, "伪币制造者", "安德烈·纪德", 59.9, "一本好书", "2022-11-15"));
                statement.execute(String.format(template, "别离歌", "陈行甲", 39, "一本好书", "2022-12-15"));
            }
        }
    }

    @Test
    public void updateTest() throws SQLException {
        try(Connection connection = this.getConnection()) {
            try(Statement statement = connection.createStatement()){
                String template = "UPDATE book SET name = '%s' WHERE id = %s";
                statement.execute(String.format(template, "Java编程入门", 1));
                statement.execute(String.format(template, "Python编程入门", 2));
                statement.execute(String.format(template, "C++编程入门", 3));
            }
        }
    }

    @Test
    public void deleteTest() throws SQLException {
        try(Connection connection = this.getConnection()) {
            try(Statement statement = connection.createStatement()){
                String template = "DELETE FROM book WHERE id = %s";
                statement.execute(String.format(template, 3));
             }
        }
    }

}
